﻿'Copyright (C) 2008 Compagnie des Transport Strasbourgeois (CTS)

'This program is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published by
'the Free Software Foundation; either version 3 of the License, or
'(at your option) any later version.

'This program is distributed in the hope that it will be useful,
'but WITHOUT ANY WARRANTY; without even the implied warranty of
'MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
'GNU General Public License for more details.

'You should have received a copy of the GNU General Public License along
'with this program; if not, write to the Free Software Foundation, Inc.,
'51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
Imports DotNetNuke
Imports System.Web.UI
Imports System.Collections.Generic
Imports System.Reflection
Imports DotNetNuke.Security.PortalSecurity
Imports DotNetNuke.Common
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports YourCompany.Modules.trombinoscope

Partial Class DesktopModules_trombinoscope_rechercher

    Inherits Entities.Modules.PortalModuleBase
    Implements Entities.Modules.IActionable
#Region "Private Members"

    Private strTemplate As String
#End Region
#Region "Constantes"
    Private chemin_photos_absolu As String
#End Region
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Page_Load runs when the control is loaded
    ''' </summary>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'On récupère le chemin absolu des photos
        chemin_photos_absolu = cheminPhotos()
        'On efface le message de critère minimum pour la recherche
        label_critereMinimum.Visible = False
        'Si on a demander à imprimer (avec le paramètre dans l'URL) : on cache les panels
        If Request.Params("imprimer") = 1 Then
            panel_impression.Visible = True
            Panel_recherche.Visible = False
            lien_organigramme.Visible = False

            DropDownList_direction.DataBind()

            'On récupère tous les paramètres dans l'URL (utiliser lorsque la personne veut imprimer)
            nom.Text = Request.Params("nom")
            prenom.Text = Request.Params("prenom")
            matricule.Text = Request.Params("matricule")
            service.SelectedValue = Request.Params("service")
            DropDownList_direction.SelectedValue = Request.Params("direction")
            fonction.Text = Request.Params("fonction")
            tri.SelectedValue = Request.Params("tri")

            'Si la personne n'a rien choisi au moment de la recherche on affiche un message d'erreur
            If nom.Text = "" And prenom.Text = "" And matricule.Text = "" And service.SelectedValue = "%" And fonction.Text = "" And DropDownList_direction.SelectedValue = "%" Then
                label_critereMinimum.Visible = True
                lancement_impression.Enabled = False
            Else
                'Sinon on effectue la requête
                Dim requete As String
                Dim requete_direction As String
                If DropDownList_direction.SelectedValue = "%" Then
                    requete_direction = "(services.direction LIKE '%' OR services.direction IS NULL)"
                Else
                    requete_direction = "services.direction LIKE '" & DropDownList_direction.SelectedValue & "'"
                End If
                requete = "SELECT salaries.nom, salaries.prenom, salaries.matricule, services.nom AS service, salaries.fonction, directions.nom as direction FROM salaries INNER JOIN (services INNER JOIN directions ON directions.id_direction = services.direction) ON salaries.service = services.id_services WHERE salaries.nom LIKE '" + Replace(nom.Text, "'", "''") + "%' AND salaries.prenom LIKE '" + Replace(prenom.Text, "'", "''") + "%' AND salaries.matricule LIKE '" + Replace(matricule.Text, "'", "''") + "%' AND services.nom LIKE '" + service.SelectedValue + "' AND " & requete_direction & " AND salaries.fonction LIKE '%" + Replace(fonction.Text, "'", "''") + "%' ORDER BY " + tri.SelectedValue
                SqlDataSource_salaries.SelectCommand = requete
            End If
        End If
    End Sub

    Protected Function cheminPhotos() As String
        Dim chaine_connexion As String
        'Récupération et association de la chaîne de connexion à la bdd
        chaine_connexion = SqlDataSource_salaries.ConnectionString
        'Récupération du chemin des photos
        Dim connection_param As SqlConnection = New SqlConnection(chaine_connexion)
        Dim commande_chemin_photos As SqlCommand = New SqlCommand("SELECT chemin_photos_absolu FROM param_trombinoscope", connection_param)
        connection_param.Open()
        Dim reader_chemin_photos As SqlDataReader = commande_chemin_photos.ExecuteReader()
        Dim chemin_photos As String
        reader_chemin_photos.Read()
        If reader_chemin_photos.HasRows Then
            chemin_photos = reader_chemin_photos.GetString(0)
        End If
        reader_chemin_photos.Close()
        connection_param.Close()
        Return String.Format("{0}", chemin_photos)
    End Function

    'Propriété utilisée directement dans le code ASP pour récupérer le chemin des photos
    Protected ReadOnly Property cheminPhotos_asp() As String
        Get
            Return chemin_photos_absolu
        End Get
    End Property

    'Requête lors du clic sur le bouton Rechercher
    Protected Sub bouton_rechercher_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bouton_rechercher.Click
        'Si la personne n'a rien choisi au moment de la recherche on affiche un message d'erreur
        If nom.Text = "" And prenom.Text = "" And matricule.Text = "" And service.SelectedValue = "%" And fonction.Text = "" And DropDownList_direction.SelectedValue = "%" Then
            label_critereMinimum.Visible = True
        Else
            'Sinon on effectue la requête
            'Si la personne n'a pas précisé de service ou si elle a choisi DG
            If service.SelectedValue = "%" Or service.SelectedValue = "DG" Then
                Dim requete As String
                Dim requete_direction As String
                If DropDownList_direction.SelectedValue = "%" Then
                    requete_direction = "(services.direction LIKE '%' OR services.direction IS NULL)"
                Else
                    requete_direction = "services.direction LIKE '" & DropDownList_direction.SelectedValue & "'"
                End If

                If service.SelectedValue = "%" Then
                    requete = "SELECT replace(salaries.nom, 'zzz', '') as nom, salaries.prenom, salaries.matricule, services.nom AS service, salaries.fonction, directions.nom as direction FROM salaries INNER JOIN (services INNER JOIN directions ON directions.id_direction = services.direction) ON salaries.service = services.id_services WHERE ltrim(salaries.nom) LIKE '" + Replace(nom.Text, "'", "''") + "%' AND ltrim(salaries.prenom) LIKE '" + Replace(prenom.Text, "'", "''") + "%' AND salaries.matricule LIKE '" + Replace(matricule.Text, "'", "''") + "%' AND ltrim(services.nom) LIKE '" + service.SelectedValue + "' AND " & requete_direction & " AND ltrim(isnull(salaries.fonction, '')) LIKE '%" + Replace(fonction.Text, "'", "''") + "%' and lower(salaries.nom + salaries.prenom) not like '%conducteur%' and lower(salaries.nom + salaries.prenom) not like '%tram%' and lower(salaries.nom + salaries.prenom) not like '%bus%' ORDER BY " + tri.SelectedValue + ", salaries.nom + '', salaries.prenom"
                Else
                    'Si la personne a choisi DG (cas particulier) : on affiche juste les membres du groupes de direction
                    requete = "SELECT salaries.nom, salaries.prenom, salaries.matricule, services.nom AS service, salaries.fonction, salaries.id_salaries, directions.nom as direction FROM salaries INNER JOIN (services INNER JOIN directions ON directions.id_direction = services.direction) ON salaries.service = services.id_services LEFT OUTER JOIN services AS services_1 ON services.parent = services_1.id_services WHERE (services.nom LIKE 'DG') OR (salaries.id_salaries = services.id_chef_de_service_1 OR salaries.id_salaries = services.id_chef_de_service_2) AND (services_1.nom LIKE 'DG') ORDER BY (CASE services.nom WHEN 'DG' THEN 1 ELSE 0 END) DESC, (CASE salaries.id_salaries WHEN services.id_chef_de_service_1 THEN 1 ELSE 0 END) DESC, (CASE salaries.id_salaries WHEN services.id_chef_de_service_2 THEN 1 ELSE 0 END) DESC, (CASE salaries.id_salaries WHEN services.id_secretaire_1 THEN 1 ELSE 0 END) DESC, (CASE salaries.id_salaries WHEN services.id_secretaire_2 THEN 1 ELSE 0 END) DESC, " + tri.SelectedValue
                End If

                SqlDataSource_salaries.SelectCommand = requete
                'Si la personne a selectionné un service on utilise la procédure stockée qui permet une liste en hiérarchie
            Else
                'On précise que l'on exécute une procédure
                SqlDataSource_salaries.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
                SqlDataSource_salaries.SelectCommand = "trombinoscope_affichageHierarchie '" & service.SelectedValue & "', '" & Replace(nom.Text, "'", "''") & "', '" & Replace(prenom.Text, "'", "''") & "', '" & Replace(fonction.Text, "'", "''") & "'"
                'On remet le paramètre pour les commandes en texte
                SqlDataSource_salaries.SelectCommandType = SqlDataSourceCommandType.Text
            End If
            End If
    End Sub

    'Initialisation de la dropdownlist (récupération des différents services dans la bdd)
    Protected Sub service_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles service.Init
        Dim chaine_connexion As String
        'Récupération et association de la chaîne de connexion à la bdd
        chaine_connexion = SqlDataSource_salaries.ConnectionString

        'Création de la connexion, de la commande et du reader (pour la lecture de la bdd)
        Dim connection_services As SqlConnection = New SqlConnection(chaine_connexion)
        Dim commande_liste_services As SqlCommand = New SqlCommand("SELECT nom FROM services ORDER BY nom", connection_services)
        connection_services.Open()
        Dim reader_liste_services As SqlDataReader = commande_liste_services.ExecuteReader()
        Dim ajout_item As ListItem
        'Lecture des lignes dans la bdd une par une et ajout à la liste avec un text et une value
        Do While reader_liste_services.Read()
            Dim nomService As String = reader_liste_services.GetString(0)
            If Left(LCase(nomService), 3) = "zzz" Then
                nomService = Right(nomService, nomService.Length - 3)
            End If
            ajout_item = New ListItem(nomService, reader_liste_services.GetString(0))
            service.Items.Add(ajout_item)
        Loop
        reader_liste_services.Close()
        connection_services.Close()
    End Sub
#Region "Optional Interfaces"

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Registers the module actions required for interfacing with the portal framework
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    ''' <history>
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public ReadOnly Property ModuleActions() As Entities.Modules.Actions.ModuleActionCollection Implements Entities.Modules.IActionable.ModuleActions
        Get
            'Code généré par DotNetNuke, gère la liste déroulante
            Dim Actions As New Entities.Modules.Actions.ModuleActionCollection
            Actions.Add(GetNextActionID, "Organigramme", "", "", "icon_users_16px.gif", Globals.NavigateURL(PortalSettings.ActiveTab.TabID, ""), False, Security.SecurityAccessLevel.View, True, False)
            Actions.Add(GetNextActionID, "Administration", "", "", "action_settings.gif", EditUrl("administration"), False, Security.SecurityAccessLevel.Admin, True, False)
            'Actions.Add(GetNextActionID, "Imprimer", "", "", "action_print.gif", Globals.NavigateURL(PortalSettings.ActiveTab.TabID, "rechercher", "mid=" & CStr(ModuleId)) & "/dnnprintmode/true/Default.aspx?SkinSrc=[G]Skins%2f_default%2fNo+Skin&ContainerSrc=[G]Containers%2f_default%2fNo+Container" & "&nom=" & matricule.Text, False, Security.SecurityAccessLevel.View, True, False)
            Return Actions
        End Get
    End Property

#End Region

    Protected Sub impression_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles impression.Click
        'Lors d'un clic sur le bouton imprimer on envoi l'utilisateur sur une page avec le style masqué et on passe les paramètres pour pouvoir refaire la requête
        Response.Redirect(Globals.NavigateURL(PortalSettings.ActiveTab.TabID, "rechercher", "mid=" & CStr(ModuleId)) & "/dnnprintmode/true/Default.aspx?SkinSrc=[G]Skins%2f_default%2fNo+Skin&ContainerSrc=[G]Containers%2f_default%2fNo+Container" & "&imprimer=1&nom=" & nom.Text & "&prenom=" & prenom.Text & "&matricule=" & matricule.Text & "&service=" & service.SelectedValue & "&direction=" & DropDownList_direction.SelectedValue & "&fonction=" & fonction.Text & "&tri=" & tri.SelectedValue)
    End Sub

    Protected Sub bouton_retour_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bouton_retour.Click
        'Lors d'un clic sur le bouton retour on renvoi l'utilisateur sur la page de recherche
        Response.Redirect(Globals.NavigateURL(PortalSettings.ActiveTab.TabID, "rechercher", "mid=" & CStr(ModuleId)))
    End Sub

    Protected Sub lien_organigramme_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lien_organigramme.Init
        lien_organigramme.NavigateUrl = Globals.NavigateURL(PortalSettings.ActiveTab.TabID, "")
    End Sub

    Protected Sub DataList_recherche_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataListItemEventArgs) Handles DataList_recherche.ItemDataBound
        If tri.SelectedIndex <> 1 And matricule.Text = "" Then
            e.Item.FindControl("matriculeLabel").Visible = False
        End If
        If tri.SelectedIndex <> 2 And service.SelectedValue <> "" Then
            e.Item.FindControl("serviceLabel").Visible = False
        End If
        If tri.SelectedIndex <> 4 And DropDownList_direction.SelectedValue <> "" Then
            e.Item.FindControl("directionLabel").Visible = False
        End If
    End Sub

    Protected Sub service_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles service.SelectedIndexChanged
        If service.SelectedValue = "%" Or service.SelectedValue = "DG" Then
            tri.Enabled = True
            matricule.Enabled = True
            DropDownList_direction.Enabled = True
        Else
            tri.SelectedIndex = 0
            tri.Enabled = False
            matricule.Text = ""
            matricule.Enabled = False
            DropDownList_direction.SelectedIndex = 0
            DropDownList_direction.Enabled = False
        End If
    End Sub
End Class

